﻿
/*********************物流项目三组************************
*  功能描述：货主合同管理
*  作者：郭辉
*  时间：2022-01-10
*  修改人：郭辉
*  修改时间：2022-01-10
*********************物流项目三组************************/
using Dapper;
using IdentityServer4.Extensions;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TodoApp.EntityFrameworkCore;
using TodoApp.ResultModel;
using TodoApp.TMS_Contract.Contracts;
using Volo.Abp.DependencyInjection;
using Volo.Abp.Domain.Repositories.Dapper;
using Volo.Abp.EntityFrameworkCore;

namespace TodoApp.TMS_Contract.Contracts
{
    public class TMS_ContractAppRepository : DapperDbContrxtRepository, ITransientDependency, ITMS_ContractAppRepository
    {
        public TMS_ContractAppRepository(IDbContextProvider<TodoAppDbContext> dbContextProvider) : base(dbContextProvider)
        {

        }
        #region 货主合同添加
        public async Task<int> CreateContracts(TMS_ContractsDto info)
        {

            var dbConnection = await GetDbConnectionAsync();
            info.CreateDate = DateTime.Now;
            info.Contract_Id= Guid.NewGuid();
            var list= await dbConnection.ExecuteAsync($"insert into TMS_Contracts values ('{info.Contract_Id}','{info.Contract_Title}','{info.Contract_Name}',{info.Contract_Line},'{info.Contract_Units}',{info.Contract_Price},{info.Contract_Chartered},{info.Contract_balance},'{info.Contract_AttentionLine}','{info.Contract_CreateDate}',{info.Contract_Money},'{info.Contract_Description}','{info.Contract_Terms}','1',{info.Contract_Type},'{info.CreateDate}',1,'{info.PeopleId}',{info.Contract_Category})",null,
                 await GetDbTransactionAsync());
            if (list > 0)
            {
                return 1;
            }
            else
            {
                return 0;
            }        
        }
        #endregion

        #region 货主合同显示分页查询
        public async Task<PageResponse<TMS_ContractsDto>> GetAllContracts(string ContractsTitle, string ContractUnits, string ContractName, string ContractAttentionLine, DateTime? ContractCreateDate, int ContractState, int pageindex = 1, int pagesize = 5)
        {
            var result = new PageResponse<TMS_ContractsDto>();
            var dbConnection = await GetDbConnectionAsync();
            var sql = "select * from TMS_Contracts a join TMS_Employees b on a.PeopleId=b.Id where a.Contract_Type=1 ";
            if (!string.IsNullOrEmpty(ContractsTitle))
            {
                sql += $" and Contract_Title like '%{ContractsTitle}%'";
            }
            if (!string.IsNullOrEmpty(ContractUnits))
            {
                sql += $" and Contract_Units = '{ContractUnits}'";
            }
            if (!string.IsNullOrEmpty(ContractName))
            {
                sql += $" and Contract_Name = '{ContractName}'";
            }
            if (!string.IsNullOrEmpty(ContractAttentionLine))
            {
                sql += $" and Contract_AttentionLine = '{ContractAttentionLine}'";
            }
            if (ContractCreateDate != null)
            {
                sql += $" and Contract_CreateDate = '{ContractCreateDate}'";
            }

            if (ContractState > 0)
            {
                sql += $" and Contract_State = {ContractState}";
            }
            var list = await dbConnection.QueryAsync<TMS_ContractsDto>(sql, transaction: await GetDbTransactionAsync());
            result.PageData = list.ToList();
            result.PageTotal = list.Count();
            sql += $" order by Contract_Id offset {(pageindex - 1) * pagesize} rows fetch next {pagesize} rows only ";
            var quer = await dbConnection.QueryAsync<TMS_ContractsDto>(sql, transaction: await GetDbTransactionAsync());
            result.PageData = quer.ToList();
            return result;

        }
        #endregion

        #region 货主合同删除
        public async Task<int> DeleteContracts(string id)
        {

            var dbConnection = await GetDbConnectionAsync();
            return await dbConnection.ExecuteAsync($"delete from TMS_Contracts where Contract_Id=@NewId", new { NewId = id },
                 await GetDbTransactionAsync());

            //var dbConnection = await GetDbConnectionAsync();
            //return await dbConnection.ExecuteAsync("update TMS_Contracts set Contract_Title=@NewName", new { NameName = name }, await GetDbTransactionAsync());
        }
        #endregion

        #region 货主合同批量删除
        public async Task<int> DeleteSContracts(string ids)
        {

            var dbConnection = await GetDbConnectionAsync();
            var list = 0;
            var obj=ids.Split(',');
            foreach (var item in obj)
            {
                list= await dbConnection.ExecuteAsync($"delete from TMS_Contracts where Contract_Id = @Ids", new { Ids = item }, await GetDbTransactionAsync());
            }
            if (list > 0)
            {
                return 1;
            }
            else
            {
                return 0;
            }

            //var dbConnection = await GetDbConnectionAsync();
            //return await dbConnection.ExecuteAsync("update TMS_Contracts set Contract_Title=@NewName", new { NameName = name }, await GetDbTransactionAsync());
        }
        #endregion

        #region 货主合同修改
        public async Task<int> UpdatePersonNames(TMS_ContractsDto info)
        {
            var dbConnection = await GetDbConnectionAsync();
            var list = await dbConnection.ExecuteAsync($"update TMS_Contracts set Contract_Title = '{info.Contract_Title}',Contract_Name='{info.Contract_Name}',Contract_Line={info.Contract_Line},Contract_Units='{info.Contract_Units}',Contract_Price={info.Contract_Price},Contract_Chartered={info.Contract_Chartered},Contract_balance={info.Contract_balance},Contract_AttentionLine='{info.Contract_AttentionLine}',Contract_CreateDate='{info.Contract_CreateDate}',Contract_Money={info.Contract_Money},Contract_Description='{info.Contract_Description}',Contract_Terms='{info.Contract_Terms}' where Contract_Id='{info.Contract_Id}'", null,
                 await GetDbTransactionAsync());
            if (list > 0)
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }
        #endregion

        #region 承运合同显示分页查询
        public async Task<PageResponse<TMS_ContractsDto>> GetAllCarriageContracts(string ContractsTitle, string ContractUnits, string ContractName, string ContractAttentionLine, DateTime? ContractCreateDate, int ContractState, int pageindex = 1, int pagesize = 5)
        {
            var result = new PageResponse<TMS_ContractsDto>();
            var dbConnection = await GetDbConnectionAsync();
            var sql = "select * from TMS_Contracts a join TMS_Employees b on a.PeopleId=b.Id where a.Contract_Type=2 ";
            if (!string.IsNullOrEmpty(ContractsTitle))
            {
                sql += $" and Contract_Title like '%{ContractsTitle}%'";
            }
            if (!string.IsNullOrEmpty(ContractUnits))
            {
                sql += $" and Contract_Units = '{ContractUnits}'";
            }
            if (!string.IsNullOrEmpty(ContractName))
            {
                sql += $" and Contract_Name = '{ContractName}'";
            }
            if (!string.IsNullOrEmpty(ContractAttentionLine))
            {
                sql += $" and Contract_AttentionLine = '{ContractAttentionLine}'";
            }
            if (ContractCreateDate != null)
            {
                sql += $" and Contract_CreateDate = '{ContractCreateDate}'";
            }

            if (ContractState > 0)
            {
                sql += $" and Contract_State = {ContractState}";
            }
            var list = await dbConnection.QueryAsync<TMS_ContractsDto>(sql, transaction: await GetDbTransactionAsync());
            result.PageData = list.ToList();
            result.PageTotal = list.Count();
            sql += $" order by Contract_Id offset {(pageindex - 1) * pagesize} rows fetch next {pagesize} rows only ";
            var quer = await dbConnection.QueryAsync<TMS_ContractsDto>(sql, transaction: await GetDbTransactionAsync());
            result.PageData = quer.ToList();
            return result;

        }
        #endregion

        #region 通用合同显示分页查询
        public async Task<PageResponse<TMS_ContractsDto>> GetAllContContracts(string ContractsTitle, string ContractUnits, string ContractName, string ContractAttentionLine, DateTime? ContractCreateDate, int ContractState, int pageindex = 1, int pagesize = 5)
        {
            var result = new PageResponse<TMS_ContractsDto>();
            var dbConnection = await GetDbConnectionAsync();
            var sql = "select * from TMS_Contracts a join TMS_Employees b on a.PeopleId=b.Id where a.Contract_Type=3 ";
            if (!string.IsNullOrEmpty(ContractsTitle))
            {
                sql += $" and Contract_Title like '%{ContractsTitle}%'";
            }
            if (!string.IsNullOrEmpty(ContractUnits))
            {
                sql += $" and Contract_Units = '{ContractUnits}'";
            }
            if (!string.IsNullOrEmpty(ContractName))
            {
                sql += $" and Contract_Name = '{ContractName}'";
            }
            if (!string.IsNullOrEmpty(ContractAttentionLine))
            {
                sql += $" and Contract_AttentionLine = '{ContractAttentionLine}'";
            }
            if (ContractCreateDate != null)
            {
                sql += $" and Contract_CreateDate = '{ContractCreateDate}'";
            }

            if (ContractState > 0)
            {
                sql += $" and Contract_State = {ContractState}";
            }
            var list = await dbConnection.QueryAsync<TMS_ContractsDto>(sql, transaction: await GetDbTransactionAsync());
            result.PageData = list.ToList();
            result.PageTotal = list.Count();
            sql += $" order by Contract_Id offset {(pageindex - 1) * pagesize} rows fetch next {pagesize} rows only ";
            var quer = await dbConnection.QueryAsync<TMS_ContractsDto>(sql, transaction: await GetDbTransactionAsync());
            result.PageData = quer.ToList();
            return result;

        }
        #endregion
    }
}
